﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Http;
using XKPlatform.Common;
using XKPlatform.Common.Extends;
using System.Linq;
using XKPlatform.DataModels;

namespace XKPlatform.Common.Helpers
{
    public class ImportTableDataHelper
    {
        public ImportTableDataHelper()
        {
        }

        /// <summary>
        /// 根据Excel文件和模版编号导出dataTable
        /// </summary>
        /// <param name="formFile">Excel数据文件</param>
        /// <param name="tempNum">模版编号</param>
        /// <returns>1、tableName；2、DBName；3、数据DataTable;4、是否可以写入解析数据；5、提示信息集合</returns>
        public static (string, string, DataTable, bool, IList<string>) ImportData(IFormFile formFile, string tempNum)
        {
            DataTable dataTable = ExcelHelper.ReadExcel(formFile.OpenReadStream(), true);
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                throw new Exception("数据文件为空！");
            }

            string sql = string.Format("SELECT sdm.DataSourceColumn,sdm.TableName,sdm.TableColumn,sdm.RegRule,sdm.DataType,sdm.IsAllowNull,sdc.DbName FROM  sysDataMap sdm inner join sysDbConnection sdc on sdm.DBId  = sdc.Id WHERE sdm.TempNum = '{0}'", tempNum);
            DataSet dataMaps = SqlHelper.Query(sql);
            if (dataMaps == null || dataMaps.Tables[0] == null || dataMaps.Tables[0].Rows.Count == 0)
            {
                throw new Exception("未找到映射关系！");
            }


            string tableName = dataMaps.Tables[0].Rows[0]["TableName"].ToString();
            string dbName = dataMaps.Tables[0].Rows[0]["DbName"].ToString();
            DataTable table = new DataTable(tableName);

            bool isSave = true;
            List<string> messages = new List<string>();

            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                string columnName = dataTable.Columns[i].ToString();
                bool flag = true;
                for (int j = 0; j < dataMaps.Tables[0].Rows.Count; j++)
                {
                    string dataSourceColumn = dataMaps.Tables[0].Rows[j]["DataSourceColumn"].ToString();
                    string tableColumn = dataMaps.Tables[0].Rows[j]["TableColumn"].ToString();
                    string dataType = dataMaps.Tables[0].Rows[j]["DataType"].ToString().ToLower();
                    if (columnName == dataSourceColumn)
                    {
                        flag = false;
                        table.Columns.Add(tableColumn, GetDataType(dataType));
                    }
                }

                if (flag)
                {
                    table.Columns.Add(columnName, GetDataType("string"));
                }
            }


            DataRowCollection dataMapRows = dataMaps.Tables[0].Rows;
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                DataRow newRow = table.NewRow();
                for (int k = 0; k < table.Columns.Count; k++)
                {
                    string columnName = table.Columns[k].ToString();
                    bool flag = true;
                    for (int j = 0; j < dataMapRows.Count; j++)
                    {
                        string dataSourceColumn = dataMapRows[j]["DataSourceColumn"].ToString();
                        bool isAllowNull = dataMapRows[j]["IsAllowNull"].ToString().ObjectToBoolean();
                        string tableColumn = dataMapRows[j]["TableColumn"].ToString();
                        string dataType = dataMapRows[j]["DataType"].ToString().ToLower();



                        if (columnName == tableColumn)
                        {
                            string dataValue = dataTable.Rows[i][dataSourceColumn].ToString();

                            string regRule = dataMapRows[j]["RegRule"].ToString();
                            // 如果允许为空并且值和正则表达式有值则也验证正则；如果不允许为空则正则表达式不为空就验证
                            if ((isAllowNull && dataValue.IsNotNullOrEmpty() && regRule.IsNotNullOrEmpty()) || (!isAllowNull && regRule.IsNotNullOrEmpty()))
                            {
                                Regex rx = new Regex(@regRule);
                                Match ma = rx.Match(dataValue);
                                if (!ma.Success)
                                {
                                    isSave = false;
                                    messages.Add(string.Format("第【{0}】行，列名【{1}】,值为【{2}】使用正则表达式【{3}】校验不通过！", i + 2, dataSourceColumn, dataValue, regRule));
                                    continue;
                                }

                            }

                            flag = false;
                            try
                            {
                                switch (dataType.ToLower())
                                {
                                    case "guid":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = Guid.NewGuid();
                                        else
                                            newRow[tableColumn] = dataValue.ToNewGuid();
                                        break;
                                    case "string":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = "";
                                        else
                                            newRow[tableColumn] = dataValue;
                                        break;
                                    case "int":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = 0;
                                        else
                                            newRow[tableColumn] = Convert.ToInt32(dataValue);
                                        break;
                                    case "decimal":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = 0;
                                        else
                                            newRow[tableColumn] = Convert.ToDecimal(dataValue);
                                        break;
                                    case "boolean":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = DBNull.Value;
                                        else
                                            newRow[tableColumn] = Convert.ToBoolean(dataValue);
                                        break;
                                    case "bool":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = DBNull.Value;
                                        else
                                            newRow[tableColumn] = Convert.ToBoolean(dataValue);
                                        break;
                                    case "datetime":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = DBNull.Value;
                                        else
                                            newRow[tableColumn] = Convert.ToDateTime(dataValue);
                                        break;
                                    case "float":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = 0;
                                        else
                                            newRow[tableColumn] = Convert.ToSingle(dataValue);
                                        break;
                                    case "double":
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = 0;
                                        else
                                            newRow[tableColumn] = Convert.ToDouble(dataValue);
                                        break;
                                    default:
                                        if (dataSourceColumn.IsNullOrEmpty() || (isAllowNull && dataValue.IsNullOrEmpty()))
                                            newRow[tableColumn] = "";
                                        else
                                            newRow[tableColumn] = dataValue;
                                        break;
                                }
                            }
                            catch
                            {
                                isSave = false;
                                messages.Add(string.Format("第【{0}】行，列名【{1}】,值为【{2}】转换为目标类型【{3}】失败！", i + 2, dataSourceColumn, dataValue, dataType));
                            }
                        }
                    }
                    if (flag)
                    {
                        newRow[columnName] = dataTable.Rows[i][columnName].ToString();
                    }
                }
                table.Rows.Add(newRow);

            }

            return (tableName, dbName, table, isSave, messages.Distinct().ToList());
        }

        public static Type GetDataType(string dataType)
        {
            switch (dataType.ToLower())
            {
                case "string":
                    return typeof(string);
                case "int":
                    return typeof(int);
                case "datetime":
                    return typeof(DateTime);
                case "guid":
                    return typeof(Guid);
                case "decimal":
                    return typeof(decimal);
                case "boolean":
                    return typeof(Boolean);
                case "bool":
                    return typeof(bool);
                case "float":
                    return typeof(float);
                case "double":
                    return typeof(double);
                default:
                    return typeof(string);
            }
        }
    }
}
